package com.business.action.oa;

import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.business.bean.system.DepartmentBean;

import net.sf.rose.boot.BootStrap;
import net.sf.rose.file.excel.poi.ExcelUtils;
import net.sf.rose.file.util.FileUtils;
import net.sf.rose.jdbc.KeyGenerator;
import net.sf.rose.jdbc.PageBean;
import net.sf.rose.jdbc.dao.BeanDAO;
import net.sf.rose.jdbc.dao.MapDAO;
import net.sf.rose.jdbc.query.BeanSQL;
import net.sf.rose.jdbc.query.MapSQL;
import net.sf.rose.jdbc.service.Service;
import net.sf.rose.util.DateFormat;
import net.sf.rose.web.util.WebUtils;

/**
 * 证书管理
 */
@Controller
@RequestMapping("/user/cert")
public class UserCertAction {

	/**
	 * 获取attend数据库的部门组织机构
	 */
	@ResponseBody
	@RequestMapping("/getDepartList.do")
	public List<DepartmentBean> getDepartList(Service service, String departID) {
		service.setCurrentDatabaseName("attend");
		BeanDAO dao = new BeanDAO(service);
		BeanSQL query = dao.getQuerySQL();
		query.setEntityClass(DepartmentBean.class);
		query.setSQL("select ng_id as departID,sz_name as departName from sys_branch ");
		if (departID == null || departID.equals("null")) {
			query.addSql("where ng_parent_id is null");
		} else {
			query.addSql("where ng_parent_id=?");
			query.addParameter(departID);
		}
		query.addSql(" and sz_code not like '%已删除%' ");
		return dao.list();
	}

	/**
	 * 平台用户查询(包含证书)
	 */
	@ResponseBody
	@RequestMapping("/page.do")
	public PageBean pageCert(HttpServletRequest request, Service service, String userName, String calendarDate,
			String status, String departID) {
		PageBean page = WebUtils.getPageBean(request);
		service.setCurrentDatabaseName("attend");
		MapDAO dao = new MapDAO(service);
		MapSQL query = dao.getQuerySQL();
		query.setPage(page);
		query.setSQL(service.getSql("attend.user.cert"));
		query.addSql(" where 1=1 ");
		if (departID != null && !departID.equals("")) {
			query.addSql("AND t3.ng_branch_id=? ");
			query.addParameter(departID);
		}
		if (userName != null && !userName.equals("")) {
			query.addSql("AND t1.sz_name=? ");
			query.addParameter(userName);
		}
		if (calendarDate != null && !calendarDate.equals("")) {
			query.addSql("AND (t2.cert_e<? or t2.cert_e is null)");
			query.addParameter(DateFormat.format(calendarDate, "yyyy-MM-dd"));
		}
		if (status != null && !status.equals("")) {
			query.addSql("AND t1.nt_user_state=? ");
			query.addParameter(status);
		}
		query.addSql("ORDER BY t3.ng_branch_id,t1.ng_id ");
		dao.list();
		return page;
	}

	/**
	 * 导出用户证书数据
	 */
	@ResponseBody
	@RequestMapping("/output.do")
	public PageBean projectOutput(Service service, String userName, String calendarDate, String status,
			String departID) {
		service.setCurrentDatabaseName("attend");
		MapDAO dao = new MapDAO(service);
		MapSQL query = dao.getQuerySQL();
		query.setSQL(service.getSql("attend.user.cert"));
		query.addSql(" where 1=1 ");
		if (departID != null && !departID.equals("")) {
			query.addSql("AND t3.ng_branch_id=? ");
			query.addParameter(departID);
		}
		if (userName != null && !userName.equals("")) {
			query.addSql("AND t1.sz_name=? ");
			query.addParameter(userName);
		}
		if (calendarDate != null && !calendarDate.equals("")) {
			query.addSql("AND (t2.cert_e<? or t2.cert_e is null)");
			query.addParameter(DateFormat.format(calendarDate, "yyyy-MM-dd"));
		}
		if (status != null && !status.equals("")) {
			query.addSql("AND t1.nt_user_state=? ");
			query.addParameter(status);
		}
		query.addSql("ORDER BY t3.ng_branch_id,t1.ng_id ");
		List<Map<String, Object>> list = dao.list();
		// 生成文件
		File file = BootStrap.getOSPath();
		file = new File(file, "resource");
		file = new File(file, "证件过期模板.xls");
		ExcelUtils excel = ExcelUtils.getTool(file);
		// 目标文件路径
		String filename = KeyGenerator.getSystemUUID() + ".xls";
		File path = FileUtils.IMAGE_PATH;
		path = new File(path, filename);
		Map<String, Object> data = new HashMap<>();
		data.put("list", list);
		if (excel.write(path, data)) {
			System.out.println(path.getAbsolutePath() + "文件已生成！");
		} else {
			System.out.println(path.getAbsolutePath() + "文件生成失败！");
		}
		PageBean page = new PageBean();
		page.addDataMap("filename", filename);
		return page;
	}

	/**
	 * 导出用户证书数据
	 */
	@ResponseBody
	@RequestMapping("/getCert.do")
	public PageBean getCert(Service service, String workNo) {
		PageBean page = new PageBean();
		service.setCurrentDatabaseName("attend");
		MapDAO dao = new MapDAO(service);
		MapSQL query = dao.getQuerySQL();
		query.setSQL(service.getSql("attend.user.cert"));
		query.addSql(" where t1.sz_employ_id=? ");
		query.addParameter(workNo);
		page.setData(dao.list());
		return page;
	}

}
